/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package crawler;

import gamePlay.Question;
import gamePlay.Player;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;

/**
 * 
 * @author Anthony Dare
 */
public class QuestionDBConnector {
	private Connection connect;
	
	/**
	 * Main class purely for testing purposes.
	 * @param args
	 */
	public static void main(String args[]) {
		try {
			System.out.println("Beginning");
			QuestionDBConnector connector = new QuestionDBConnector();
			System.out.println("QuestionDBConnector Created");
			// 69.81.205.167
			connector.initialize("jdbc:mysql://localhost/groupwork_db",
					"groupwork", "smiles");
			System.out.println("Connection Initialized");
			
			List<PageQnA> lpqa = new CopyOnWriteArrayList<PageQnA>();
			PageQnA pqa = new PageQnA("http://test.url/", "Test Title");
			List<String> q = new CopyOnWriteArrayList<String>();
			List<Integer> a = new CopyOnWriteArrayList<Integer>();
			q.add("This is a test question about the number ___.");
			a.add(5);
			pqa.addQnA(q, a);
			lpqa.add(pqa);
			System.out.println("PageQnA Created");
			connector.insertPageQnA(lpqa);
			System.out.println("PageQnA Sent");
			
			connector.generateQuestionFile();
			connector.close();
			System.out.println("Connection Closed");
		} catch (Exception e) {
			System.out.println(e);
		}
	}
	/**
	 * This initializes the connection to the database.  It will throw an exception
	 * if there is an issue connection to the DB.
	 * @param dbUrl The URL for the DB. Probably something like: jdbc:mysql://HostName/DBName
	 * @param username The username for the DB.
	 * @param password The password for the DB.
	 * @throws Exception
	 */
	public void initialize(String dbUrl, String username, String password)
			throws Exception {
		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		connect = DriverManager.getConnection(dbUrl, username, password);
	}

	public void insertPageQnA(List<PageQnA> tasks) throws Exception {
		while (!tasks.isEmpty()) {
			PageQnA current = tasks.remove(0);
			List<String> questions = current.getQuestions();
			List<Integer> answers = current.getAnswers();
			PreparedStatement pt = connect
					.prepareStatement("insert into PageAndTitle (PT_ID, URL, Title) values (?,?,?)");
			pt.setInt(1, current.getUrl().hashCode());
			pt.setString(2, current.getUrl());
			pt.setString(3, current.getTitle());
			try {
				pt.executeUpdate();
			} catch (Exception e) {
				System.out.println(e);
			}
			while (!current.isQnAEmpty()) {
				PreparedStatement qa = connect
						.prepareStatement("insert into QuestionsAndAnswers (QA_ID, Question, Answer, PT_ID) values (?,?,?,?)");
				qa.setInt(1, questions.get(0).hashCode());
				qa.setString(2, questions.remove(0));
				qa.setInt(3, answers.remove(0));
				qa.setInt(4, current.getUrl().hashCode());
				try {
					qa.executeUpdate();
				} catch (Exception e) {
					System.out.println(e);
				}
			}
		}
	}
	/**
	 * This Method gets all the questions and answers from the DB.
	 * @return A List of Question objects.
	 * @throws Exception
	 */
	public List<Question> getQuestions() throws Exception{
		List<Question> questions = new ArrayList<Question>();
		PreparedStatement qa = connect.prepareStatement("select * from QuestionsAndAnswers");
		ResultSet results = qa.executeQuery();
		while(results.next()){
			questions.add(new Question(results.getString(2), results.getInt(3)));
		}
		return questions;
	}
	
	/**
	 * Takes a number(numOfQs) from NetworkGame and pulls and returns a 
	 * numOfQs amount of random questions from the database. 
	 * 
	 * @param numOfQs
	 * @return questions
	 * @throws Exception
	 */
	public List<Question> getRandomQuestions(int numOfQs) throws Exception{
		// (Anthony) this was throwing all sorts of exceptions.
//		ArrayList<Question> questions = new ArrayList<Question>();
//		PreparedStatement qa = connect.prepareStatement("select * from QuestionsAndAnswers order by rand() limit '" + numOfQs + "'");
//		ResultSet results = qa.executeQuery();
//		while(results.next()) {
//			questions.add(new Question(results.getString(2), results.getInt(3)));
//		}
//		return questions;
		
		// (Anthony) Code Begins
		List<Question> tempQuestions = getQuestions();
		Collections.shuffle(tempQuestions);
		List<Question> questions = new ArrayList<Question>();
		for(int i = 0; i < numOfQs; i++){
			questions.add(tempQuestions.get(i));
		}
		return questions;
		// (Anthony) Code Ends
	}
	
	//Jim Choinoski
	public List<Question> getUserQuestions(String User_Email) throws Exception{
		List<Question> userQuestions = new ArrayList<Question>();
		PreparedStatement uqa = connect.prepareStatement("select * from QuestionsAndAnswers where QuestionsAndAnswers.User_Email=(?)");
		uqa.setString(1, User_Email);
		ResultSet results = uqa.executeQuery();
		while(results.next()){
			userQuestions.add(new Question(results.getString(2), results.getInt(3)));
		}
		return userQuestions;
	}
	
	//Jim Choinoski
	public List<Question> getCategoryQuestions(int CAT_ID) throws Exception{
		List<Question> categoryQuestions = new ArrayList<Question>();
		PreparedStatement cqa = connect.prepareStatement("select * from QuestionsAndAnswers where QuestionsAndAnswers.QA_ID in select QA_ID from HasCategories where CAT_ID=(?)");
		cqa.setInt(1, CAT_ID);
		ResultSet results = cqa.executeQuery();
		while(results.next()){
			categoryQuestions.add(new Question(results.getString(2), results.getInt(3)));
		}
		return categoryQuestions;
	}
	
	public void generateQuestionFile() throws Exception{
		List<Question> questions = getQuestions();
		Collections.shuffle(questions);
		FileWriter fstream = new FileWriter("Questions.txt");
		BufferedWriter out = new BufferedWriter(fstream);
		for(int i = 0; i < questions.size(); i++){
			out.write(questions.get(i).getText());
			out.newLine();
			out.write(Integer.toString(questions.get(i).getAnswer()));
			out.newLine();
		}
		out.close();
	}
	
	public void generateQuestionFile(List<PageQnA> tasks) throws Exception{
		FileWriter fstream = new FileWriter("Questions.txt");
		BufferedWriter out = new BufferedWriter(fstream);
		
		for(int i = 0; i < tasks.size(); i++){
			PageQnA current = tasks.get(i);
			for(int j = 0; j < current.getQuestions().size(); j++){
				out.write(current.getQuestions().get(j) + '\n');
				out.write(current.getAnswers().get(j) + '\n');
			}
		}
		out.close();
	}
	
	public ResultSet executeQuery(String query) throws Exception {
		Statement statement = connect.createStatement();
		return statement.executeQuery(query);
	}

	public int executeUpdate(String update) throws Exception {
		Statement statement = connect.createStatement();
		return statement.executeUpdate(update);
	}

	public void commit() throws Exception {
		if (!connect.getAutoCommit()) {
			connect.commit();
		}
	}

	public void close() throws Exception {
		connect.close();
	}

	public boolean isValid() {
		return connect != null;
	}
	
	//Jim C.
	public List<Player> getHighScoreList() throws Exception{
		List<Player> players = new ArrayList<Player>();
		PreparedStatement p = connect.prepareStatement("SELECT * FROM HighScores ORDER BY Score DESC");
		ResultSet results = p.executeQuery();
		while(results.next()){
			Player temp = new Player(results.getString(2), 0);
			temp.setPlayerID(results.getInt(1));
			temp.setGame_chips(results.getInt(3));
			players.add(temp);
		}
		return players;
	}
	
	//Jim C.
	public void removeNameFromHighScoreList (int playerID) throws Exception{
		PreparedStatement x = connect.prepareStatement("Delete from HighScores where HS_ID=(?)");
		x.setInt(1, playerID);
		x.execute();
	}
	
	//Jim C.
	public void putNameInHighScoreList (int playerID, String name, int score) throws Exception{
		PreparedStatement y = connect.prepareStatement("insert into HighScores (HS_ID, User_Name, Score) values (?,?,?)");
		y.setInt(1, playerID);
		y.setString(2, name);
		y.setInt(3, score);
		y.execute();
	}
}
